/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package df.bean.report;

/**
 *
 * @author
 */
public class ReportQuery {

    String hospital_code;
    String from_date;
    String to_date;
    String doctor_code;
    String doctor_profile_code;
    String doctor_category;
    String order_item;
    String order_item_category;
    String doctor_department;
    String transaction_type;
    String admission_type;
    String invoice_no;
    String doc_type;
    String transaction_module;

    public void setHospitalCode(String n){
        this.hospital_code = n;
    }
    public void setFromDate(String n){
        this.from_date = n;
    }
    public void setToDate(String n){
        this.to_date = n;
    }
    public void setDoctorProfileCode(String n){
    	this.doctor_profile_code = n;
    }
    public void setDoctorCode(String n){
        this.doctor_code = n;
    }
    public void setDoctorCategory(String n){
        this.doctor_category = n;
    }
    public void setOrderItem(String n){
        this.order_item = n;
    }
    public void setOrderItemCategory(String n){
        this.order_item_category = n;
    }
    public void setDoctorDepartment(String n){
        this.doctor_department = n;
    }
    public void setTransactionType(String n){
        this.transaction_type = n;
    }
    public void setInvoiceNo(String n){
    	this.invoice_no = n;
    }
    public void setAdmissionType(String n){
        this.admission_type = n;
    }
    public void setDocType(String n){
        this.doc_type = n;
    }
    public void setTransactionModule(String n){
        this.transaction_module = n;
    }
    public String getReport(String n){
        String qr = "";
        if(n.equals("SummaryDaily")){
            qr = summaryDaily();
        }
        if(n.equals("ImportTransaction")){
            qr = importTransaction();
        }
        if(n.equals("NoVerifyTransaction")){
        	qr = noVerifyTransaction();
        }
        return qr;
    }
    private String noVerifyTransaction(){
    	return "SELECT I.HOSPITAL_CODE, CASE WHEN I.ADMISSION_TYPE_CODE = 'I' THEN 'IPD' ELSE 'OPD' END AS ADMISSION_TYPE_CODE, "+
    	"I.INVOICE_NO, I.INVOICE_DATE, I.LINE_NO, I.RECEIPT_TYPE_CODE, I.HN_NO, I.PATIENT_NAME, I.EPISODE_NO, "+
    	"I.ORDER_ITEM_CODE, OI.DESCRIPTION_THAI, I.DOCTOR_CODE, I.AMOUNT_AFT_DISCOUNT "+
    	"FROM TRN_DAILY I LEFT OUTER JOIN ORDER_ITEM OI ON I.ORDER_ITEM_CODE = OI.CODE AND I.HOSPITAL_CODE = OI.HOSPITAL_CODE "+
    	"WHERE (I.INVOICE_DATE BETWEEN '"+this.from_date+"' AND '"+this.to_date+"') AND "+
    	"I.LINE_NO NOT IN (SELECT SUBSTRING(LINE_NO,1,LEN(LINE_NO)-1) " +
    	"FROM TRN_DAILY WHERE (INVOICE_DATE BETWEEN '"+this.from_date+"' AND '"+this.to_date+"') AND INVOICE_TYPE = 'RESULT') AND "+
    	"I.INVOICE_TYPE = 'ORDER' AND OI.IS_COMPUTE = 'Y' AND "+
    	"I.ADMISSION_TYPE_CODE LIKE '"+this.admission_type+"' AND "+
    	"I.TRANSACTION_TYPE LIKE '"+this.transaction_type+"' AND "+
    	"I.ORDER_ITEM_CODE LIKE '"+this.order_item+"' AND "+
    	"I.ORDER_ITEM_CATEGORY_CODE LIKE '"+this.order_item_category+"' AND "+
    	"I.RECEIPT_DEPARTMENT_CODE LIKE '"+this.doctor_department+"' AND "+
    	"I.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
    	"ORDER BY I.ADMISSION_TYPE_CODE, I.INVOICE_NO, I.LINE_NO";
    }
    private String importTransaction(){
        return "SELECT I.HOSPITAL_CODE, CASE WHEN I.ADMISSION_TYPE_CODE = 'I' THEN 'IPD' ELSE 'OPD' END AS ADMISSION_TYPE_CODE, "+
        "I.BILL_NO, I.BILL_DATE, I.LINE_NO, I.RECEIPT_TYPE_CODE, I.HN_NO, I.PATIENT_NAME, I.EPISODE_NO, I.PAYOR_CODE, "+
        "I.PAYOR_NAME, I.ORDER_ITEM_CODE, I.ORDER_ITEM_DESCRIPTION, I.DOCTOR_CODE, I.DOCTOR_NAME, I.AMOUNT_BEF_DISCOUNT, "+
        "I.PATIENT_LOCATION_CODE, I.VERIFIED_DATE, I.VERIFIED_TIME, "+
        "I.AMOUNT_OF_DISCOUNT "+
        "FROM INT_HIS_BILL I LEFT OUTER JOIN ORDER_ITEM OI ON I.ORDER_ITEM_CODE = OI.CODE AND I.HOSPITAL_CODE = OI.HOSPITAL_CODE "+
        "WHERE (I.BILL_DATE BETWEEN '"+this.from_date+"' AND '"+this.to_date+"') AND "+
        "I.DOCTOR_CODE LIKE '"+this.doctor_code+"' AND "+
        "I.ADMISSION_TYPE_CODE LIKE '"+this.admission_type+"' AND "+
        "I.TRANSACTION_TYPE LIKE '"+this.transaction_type+"' AND "+
        "I.ORDER_ITEM_CODE LIKE '"+this.order_item+"' AND "+
        "I.HOSPITAL_CODE LIKE '"+this.hospital_code+"' "+
        "ORDER BY I.ADMISSION_TYPE_CODE, I.BILL_NO, I.LINE_NO";
    }
    private String summaryDaily(){
        return "SELECT S.HOSPITAL_CODE, CASE WHEN S.ADMISSION_TYPE_CODE = 'I' THEN 'IPD' ELSE 'OPD' END AS ADMISSION_TYPE_CODE, "+
        "S.TRANSACTION_TYPE, S.TRANSACTION_MODULE, S.INVOICE_NO, S.INVOICE_DATE,S.RECEIPT_TYPE_CODE, " +
        "S.HN_NO, S.LINE_NO, S.PATIENT_NAME, S.EPISODE_NO, S.PAYOR_OFFICE_CODE, S.PAYOR_OFFICE_NAME, " +
        "S.ORDER_ITEM_CODE, S.DOCTOR_CATEGORY_CODE, S.ORDER_ITEM_DESCRIPTION, S.DOCTOR_CODE, D.NAME_THAI, " +
        "S.AMOUNT_AFT_DISCOUNT, S.DOCTOR_DEPARTMENT_CODE, S.NOR_ALLOCATE_AMT, S.DR_AMT, S.HP_AMT, " +
        "S.DR_TAX_400+S.DR_TAX_401+S.DR_TAX_402+S.DR_TAX_406 AS DR_TAX_AMT, S.TAX_TYPE_CODE, S.NOR_ALLOCATE_PCT "+
        "FROM TRN_DAILY S LEFT OUTER JOIN DOCTOR D ON S.DOCTOR_CODE = D.CODE AND S.HOSPITAL_CODE = D.HOSPITAL_CODE "+
        "LEFT OUTER JOIN ORDER_ITEM OI ON S.ORDER_ITEM_CODE = OI.CODE AND S.HOSPITAL_CODE = OI.HOSPITAL_CODE "+
        "WHERE ((S.INVOICE_DATE BETWEEN '"+from_date+"' AND '"+to_date+"') or (S.RECEIPT_DATE BETWEEN '"+from_date+"' AND '"+to_date+"')) AND "+
        "S.TRANSACTION_MODULE LIKE '"+transaction_module+"' AND "+
        "S.ADMISSION_TYPE_CODE LIKE '"+admission_type+"' AND "+
        "S.TRANSACTION_TYPE LIKE '"+transaction_type+"' AND "+
        "D.DOCTOR_PROFILE_CODE LIKE '"+doctor_profile_code+"' AND "+
        "S.DOCTOR_CODE LIKE '"+doctor_code+"' AND "+
        "D.DEPARTMENT_CODE LIKE '"+doctor_department+"' AND "+
        "D.DOCTOR_CATEGORY_CODE LIKE '"+this.doctor_category+"' AND "+
        "S.ORDER_ITEM_CODE LIKE '"+order_item+"' AND "+
        "OI.ORDER_ITEM_CATEGORY_CODE LIKE '"+order_item_category+"' AND "+
        "S.INVOICE_NO LIKE '"+invoice_no+"' AND "+
        "S.IS_WRITE_OFF LIKE '"+doc_type+"' AND "+
        "S.ACTIVE = '1' AND "+
        "S.HOSPITAL_CODE LIKE '"+hospital_code+"' "+
        "ORDER BY S.ADMISSION_TYPE_CODE, S.INVOICE_NO, S.LINE_NO";
    }
}